package com.saltlux.storm.study.bbs.impl.h2;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.saltlux.storm.study.bbs.BoardDAO;
import com.saltlux.storm.study.bbs.BoardDTO;

public class BoardDAOImpl implements BoardDAO {
	public BoardDAOImpl() {
		boolean ensureTable = false;
		ensureTable = checkTable();
		if (!ensureTable) {
			createBoardTable();
			ensureTable = true;
		}
	}

	private boolean createBoardTable() {
		return DatabaseHelper
				.execute("create table boards (id IDENTITY, title VARCHAR(100), description  VARCHAR(100), createdAt VARCHAR(100), createdBy VARCHAR(100), lastModifiedAt VARCHAR(100), lastModifiedBy VARCHAR(100))");
	}

	private boolean checkTable() {
		boolean ensureTable = false;
		List<Map<String, String>> rs = DatabaseHelper
				.executeQuery("show tables");

		for (Map<String, String> eachMap : rs) {
			String tableName = eachMap.get("TABLE_NAME");
			if (tableName.equalsIgnoreCase("boards")) {
				ensureTable = true;
				break;
			}
		}
		return ensureTable;
	}

	public void put(BoardDTO boardDTO) {
		String queryString = "insert into boards (title, description, createdAt , createdBy, lastModifiedAt , lastModifiedBy)"
				+ " values ('"
				+ boardDTO.getTitle()
				+ "', '"
				+ boardDTO.getDescription()
				+ "', '"
				+ boardDTO.getCreatedAt()
				+ "', '"
				+ boardDTO.getCreatedBy()
				+ "' , '"
				+ boardDTO.getLastModifiedAt()
				+ "', '"
				+ boardDTO.getLastModifiedBy() + "')";
		Long id = DatabaseHelper.executeInsertSingle(queryString);
		boardDTO.setId(id.toString());
	}

	public void deleteById(String id) {
		DatabaseHelper.execute("delete from boards where id = " + id);
	}

	public BoardDTO findById(String id) {
		String queryString = "select * from boards where id = " + id;
		List<Map<String, String>> list = DatabaseHelper
				.executeQuery(queryString);
		if (list.size() != 1)
			return null;

		Map<String, String> map = list.get(0);
		return BoardDTO.valueOf(map);
	}

	public void modify(BoardDTO boardDTO) {
		DatabaseHelper.execute("UPDATE BOARDS SET TITLE = '"
				+ boardDTO.getTitle() + "', DESCRIPTION = '"
				+ boardDTO.getDescription() + "', CREATEDAT = '"
				+ boardDTO.getCreatedAt() + "', CREATEDBY = '"
				+ boardDTO.getCreatedBy() + "', LASTMODIFIEDAT = '"
				+ boardDTO.getLastModifiedAt() + "', LASTMODIFIEDBY = '"
				+ boardDTO.getLastModifiedBy() + "' WHERE ID = "
				+ boardDTO.getId());
	}

	public List<BoardDTO> findByTitle(String title) {
		String queryString = "SELECT * FROM BOARDS WHERE UPPER(TITLE) LIKE '%"+title.toUpperCase()+"%'" ;
		List<Map<String, String>> list = DatabaseHelper.executeQuery(queryString);
		
		List<BoardDTO> result = new ArrayList<BoardDTO>();
		for (int i = 0; i <list.size() ; i++) {
			Map<String, String> map = list.get(i);
			result.add(BoardDTO.valueOf(map));
		}
		return result;
	}

}
